package com.utils.info.util.hhy;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

public class ExcelUtil {
    /**
     * 写入数据
     * @param sheet
     * @param sheetRow
     * @param col
     * @param data
     */
    public static void writeDate(HSSFSheet sheet, HSSFRow sheetRow, int col, String data) {
        // 默认列宽
        sheet.setDefaultColumnWidth(20);
        // 插入数据
        HSSFCell sheetRowCell = sheetRow.createCell(col);
        sheetRowCell.setCellValue(data);
    }

    /**
     * 获取一个sheet对象
     * @param workbook
     * @param sheet
     * @return
     */
    public static HSSFSheet getHSSFSheet(HSSFWorkbook workbook, String sheet){
        return workbook.createSheet(sheet);
    }
    public static HSSFWorkbook getWorkBook(){
        return new HSSFWorkbook();
    }

    /**
     *
     * 写入数据
     * @param
     * @param tableHeader 表头
     * @param data 数据
     * @return HSSFSheet对象
     */
    public static void writeByRow(String title, List<String> tableHeader, List<List<String>> data,HttpServletResponse resp, String fileName, int width, int height){
        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet("sheet1");
        // 标题样式
        HSSFCellStyle titleStyle = workBook.createCellStyle();
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 居中
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = workBook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        //粗体显示
        font.setFontHeightInPoints((short) 12); // 字体大小
        titleStyle.setFont(font);
        // 表头样式
        HSSFCellStyle tableHeaderStyle = workBook.createCellStyle();
        HSSFFont font1 = workBook.createFont();
        font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        tableHeaderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 居中
        tableHeaderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        tableHeaderStyle.setFont(font1);
        // 内容样式
        HSSFCellStyle dataStyle = workBook.createCellStyle();
        dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置行高
        sheet.setDefaultColumnWidth(width);

        int startRow = 0;
        if(title!=null){
            // 创建标题
            startRow = 1;
            int size = tableHeader.size();
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, size-1);
            sheet.addMergedRegion(cra);
            HSSFRow row = sheet.createRow(0);
            row.setHeight((short) height);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(title);
            cell.setCellStyle(titleStyle);

        }

        // 创建表头
        HSSFRow headerRow = sheet.createRow(startRow);
        headerRow.setHeight((short) height);
        for (int i = 0; i < tableHeader.size(); i++) {
            HSSFCell cell = headerRow.createCell(i);
            cell.setCellValue(tableHeader.get(i));
            cell.setCellStyle(tableHeaderStyle);
        }
        // 循环数据
        for (int i = 0; i < data.size(); i++) {
            // 每一行的数据
            List<String> rowData = data.get(i);
            // 从第三行开始写数据
            HSSFRow row = sheet.createRow(i+startRow+1);
            row.setHeight((short) height);
            for (int i1 = 0; i1 < rowData.size(); i1++) {
                HSSFCell cell = row.createCell(i1);
                cell.setCellValue(rowData.get(i1));
                cell.setCellStyle(dataStyle);
            }
        }

        if(resp!=null && fileName!=null){
            // 处理响应中文的乱码问题
            resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
            try {
                fileName=new String(fileName.getBytes("UTF-8"),"iso8859-1");
                resp.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName,"iso8859-1") + DateUtil.getFormatTimeForYMDHMS(DateUtil.getTime()) + ".xls");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
        try {
            workBook.write(resp.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 按列写数据
     * @param map 存放HSSFRow对象的map
     * @param data 写入的数据 一列的
     * @param row 开始的行数
     * @param column 写在第几列
     * @param style 样式
     */
    public static void writeByColumn(Map<Integer, HSSFRow> map, List<String> data,Integer row, Integer column, HSSFCellStyle style){
        for (int i = 0; i < data.size(); i++) {
            HSSFCell cell = map.get(i + row).createCell(column);
            cell.setCellValue(data.get(i));
            cell.setCellStyle(style);
        }
    }

}